/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Helpers;

import entity.Boletin;
import entity.Dato;
import entity.Inddeuda;
import entity.Permiso;
import entity.Trm;
import entity.Video;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Date;

/**
 *
 * @author Carito
 */
@Stateless
public class ListaDatos_h implements ListaDatos_hLocal {

    @PersistenceContext(unitName = "SAIE-ejbPU")
    private EntityManager em;
    
   public List<Dato> Listar (String idEmpresa, String idUsuario, String filtro) 
    {
         List <Permiso> p = new ArrayList<Permiso>(); 
       Query qp= em.createQuery("SELECT p FROM Permiso p WHERE p.usuario.usuarioPK.idusuario = :A"
               + " AND p.usuario.usuarioPK.idempresa = :B");
        qp.setParameter("A", new BigInteger (idUsuario));
       qp.setParameter("B", new BigInteger (idEmpresa));
       
       try{
            p=(List <Permiso>)qp.getResultList();
            }catch(NoResultException e) {
             return null;
        }
   
       List <Dato> d = new ArrayList<Dato>(),d1=new ArrayList<Dato>() ;
       
        if (filtro.equalsIgnoreCase("TODO")){
            Query q= em.createQuery("SELECT d FROM Dato d");
          
            try{
            d=(List <Dato>)q.getResultList();
            }catch(NoResultException e) {
             return null;
            }
           for (Dato element: d)
           {
               
              if (element.getIdpermiso() != null){
                   
               Query qn= em.createQuery("SELECT p FROM Permiso p WHERE p.idpermiso = :A");
               qn.setParameter("A", element.getIdpermiso().getIdpermiso());
               Permiso permisoDato=(Permiso)qn.getSingleResult();  
               String available=permisoDato.getEnHorarioDisponible()+"";
               if (p.contains(permisoDato) && available.equals("1"))
                       {
                          d1.add(element);
                       }
               
              }
               
           }
            
        }else if (filtro.equalsIgnoreCase("TRM"))
        {
           Query q1= em.createQuery("SELECT t FROM Trm t"); 
           
            List<Trm> trm;
            try{
            trm=(List <Trm>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Trm dato : trm) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getTrmPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        
        }else if (filtro.equalsIgnoreCase("DEUDA"))
            {
           Query q1= em.createQuery("SELECT t FROM Inddeuda t"); 
           
            List<Inddeuda> list;
            try{
            list=(List <Inddeuda>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Inddeuda dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getInddeudaPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        }else if (filtro.equalsIgnoreCase("VIDEO"))
            {
           Query q1= em.createQuery("SELECT v FROM Video v"); 
           
            List<Video> list;
            try{
            list=(List <Video>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Video dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getVideoPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        }else if (filtro.equalsIgnoreCase("BOLETIN"))
            {
           Query q1= em.createQuery("SELECT b FROM Boletin b"); 
           
            List<Boletin> list;
            try{
            list=(List <Boletin>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Boletin dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getBoletinPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
             
            }
        return d;
    }
    
    public Trm DatoTRM (String  idDato)
    {
        
        Trm t;
        Query q= em.createQuery("SELECT t FROM Trm t WHERE t.trmPK.idtrm = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Trm)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        return t;
    }   

     public List<Trm> RangoTRM (Date  dtInicial, Date dtFinal)
    {
        List<Trm> t;
        Query q= em.createQuery("SELECT t FROM Trm t WHERE t.fecha between :A and :B");
        q.setParameter("A", dtInicial);
        q.setParameter("B", dtFinal);  
        try{
          t=q.getResultList();
        }catch(NoResultException e) {
          return null;
        }
        
        return t;
    }   
     
     public String RangoTRMS (Date  dtInicial, Date dtFinal) {
     
         StringBuilder sb = new StringBuilder();
         List<Trm> objs = RangoTRM(dtInicial, dtFinal);
         
         if (objs != null) {
             for (Trm t : objs) {
                 SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy hh:mm");
                 sb.append(String.format("%d;%d;%s", t.getTrmPK().getIdtrm(), t.getValor(), df.format(t.getFecha())));
                 sb.append(System.lineSeparator());
              
            }
         }
         
         return sb.toString();
     }
    
     public Inddeuda DatoDeuda (String  idDato)
    {
        
        Inddeuda object;
        Query q= em.createQuery("SELECT d FROM Inddeuda d WHERE d.inddeudaPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          object=(Inddeuda)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        return object;
    } 
     
      public Video DatoVideo (String  idDato)
    {
        
        Video t;
        Query q= em.createQuery("SELECT v FROM Video v WHERE v.videoPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Video)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        return t;
    } 
      
       public Boletin DatoBoletin (String  idDato)
    {
        
        Boletin t;
        Query q= em.createQuery("SELECT b FROM Boletin b WHERE b.boletinPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Boletin)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        return t;
    } 
     
   //--Return String 
   public String Listars (String idEmpresa, String idUsuario,String filtro) 
    {
         List <Permiso> p = new ArrayList<Permiso>(); 
       Query qp= em.createQuery("SELECT p FROM Permiso p WHERE p.usuario.usuarioPK.idusuario = :A"
               + " AND p.usuario.usuarioPK.idempresa = :B");
        qp.setParameter("A", new BigInteger (idUsuario));
       qp.setParameter("B", new BigInteger (idEmpresa));
       
       try{
            p=(List <Permiso>)qp.getResultList();
            }catch(NoResultException e) {
             return null;
        }
   
       List <Dato> d = new ArrayList<Dato>(),d1=new ArrayList<Dato>() ;
       
        if (filtro.equalsIgnoreCase("TODO")){
            Query q= em.createQuery("SELECT d FROM Dato d");
          
            try{
            d=(List <Dato>)q.getResultList();
            }catch(NoResultException e) {
             return null;
            }
           for (Dato element: d)
           {
               
              if (element.getIdpermiso() != null){
                   
               Query qn= em.createQuery("SELECT p FROM Permiso p WHERE p.idpermiso = :A");
               qn.setParameter("A", element.getIdpermiso().getIdpermiso());
               Permiso permisoDato=(Permiso)qn.getSingleResult();  
               String available=permisoDato.getEnHorarioDisponible()+"";
               if (p.contains(permisoDato) && available.equals("1"))
                       {
                          d1.add(element);
                       }
               
              }
               
           }
            
        }
        else if (filtro.equalsIgnoreCase("TRM"))
        {
           Query q1= em.createQuery("SELECT t FROM Trm t"); 
           
            List<Trm> trm;
            try{
            trm=(List <Trm>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Trm dato : trm) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getTrmPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        
        }else if (filtro.equalsIgnoreCase("DEUDA"))
            {
           Query q1= em.createQuery("SELECT t FROM Inddeuda t"); 
           
            List<Inddeuda> list;
            try{
            list=(List <Inddeuda>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Inddeuda dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getInddeudaPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        }else if (filtro.equalsIgnoreCase("VIDEO"))
            {
           Query q1= em.createQuery("SELECT v FROM Video v"); 
           
            List<Video> list;
            try{
            list=(List <Video>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Video dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getVideoPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
        }else if (filtro.equalsIgnoreCase("BOLETIN"))
            {
           Query q1= em.createQuery("SELECT b FROM Boletin b"); 
           
            List<Boletin> list;
            try{
            list=(List <Boletin>)q1.getResultList();
            }catch(NoResultException e) {
             return null;
            }
            
            for (Boletin dato : list) {
                Query q= em.createQuery("SELECT d FROM Dato d WHERE d.iddato = :A");
                q.setParameter("A", dato.getBoletinPK().getIddato());
                d.add((Dato)  q.getSingleResult() );
            }
             
            }
        String result="";
        for(Dato element:d1)
        {
            result+= element.getIddato()+";"+
                     element.getDescripcion()+";"+"_";
        }
        return result;
    }
    
    public String DatoTRMS (String  idDato)
    {
        
        Trm t;
        Query q= em.createQuery("SELECT t FROM Trm t WHERE t.trmPK.idtrm = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Trm)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
         String result="";
       
         result+= t.getTrmPK().getIdtrm()+";"+
                  t.getValor()+";"+
                  t.getFecha();
         return result;
    }   
    
     public String DatoDeudas (String  idDato)
    {
        
        Inddeuda object;
        Query q= em.createQuery("SELECT d FROM Inddeuda d WHERE d.inddeudaPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          object=(Inddeuda)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        String result="";
       
         result+= object.getInddeudaPK().getIdinddeuda()+";"+
                  object.getTipotitulo()+";"+
                  object.getTasapromedio()+";"+
                  object.getUltimatasa()+";"+
                  object.getFecha();
         return result;
    } 
     
      public String DatoVideos (String  idDato)
    {
        
        Video t;
        Query q= em.createQuery("SELECT v FROM Video v WHERE v.videoPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Video)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
        String result="";
       
         result+= t.getVideoPK().getId()+";"+
                  t.getNombre()+";"+
                  t.getFecha()+";"+
                  t.getAutor()+";"+
                  t.getTamano();
        
         return result;
    } 
      
       public String DatoBoletins (String  idDato)
    {
        
        Boletin t;
        Query q= em.createQuery("SELECT b FROM Boletin b WHERE b.boletinPK.iddato = :A");
        q.setParameter("A", new BigInteger(idDato));
          
        try{
          t=(Boletin)q.getSingleResult();
        }catch(NoResultException e) {
          return null;
        }
        
           String result="";
       
         result+= t.getBoletinPK().getIdboletin()+";"+
                  t.getNombre()+";"+
                  t.getActividad()+";"+
                  t.getLocalizacion()+";"+
                  t.getTiempo();
        
         return result;
    }    
    
}
